Skip Headers

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) Part Number B14192-03 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 4.8 Reporting on Backups and Database Schema

The RMAN `REPORT` command analyzes the available backups and your database to answer imoprtant questions, such as:

- Which files need a backup?

- Which files have had unrecoverable operations performed on them?

- Which backups are obsolete and can be deleted?

- What was the physical schema of the database at some previous time?

- Which files have not been backed up recently?

  Note: The results of `REPORT` are only correct if the RMAN repository has an accurate record of available backups and the state of your database. For example, if backups have been deleted from disk or tape outside of RMAN, reports generated by RMAN do not automatically reflect these changes. If some backups recorded in the RMAN repository have been deleted, or if some are temporarily unavailable due to a storage device being offline or media being unavailable, you can use the `CROSSCHECK` commands to update the status of all backups, or the `CHANGE`, `CATALOG`, `UNCATALOG` and `DELETE` commands to directly set the status of individual backups. See Chapter 8, "Recovery Manager Maintenance Tasks" to learn how to update the RMAN repository to reflect your actual available backups.

This section contains the following topics:

- About Reports of RMAN Backups

- Reporting on Files Needing a Backup Under a Retention Policy

- Reporting on Datafiles Affected by Unrecoverable Operations

- Reporting Obsolete Backups

- Reporting on the Database Schema

## 4.8.1 About Reports of RMAN Backups

Reports enable you to confirm that your backup and recovery strategy is in fact meeting your requirements for database recoverability. The two major forms of `REPORT` used to determine whether your database is recoverable are:

- `REPORT` `NEED` `BACKUP`

  Reports which database files need to be backed up to meet a configured or specified retention policy

- `REPORT` `UNRECOVERABLE`

  Reports which database files require backup because they have been affected by some NOLOGGING operation such as a direct-path insert

Note: The `REPORT` command use the information in the RMAN repository as the basis for their output. If backups have been manipulated outside of the control of RMAN (for example, if you believe some disk-based backups have been deleted, or tape backups are temporarily unavailable or permanently lost) then see Chapter 8, "Recovery Manager Maintenance Tasks" for details on how to update the RMAN repository record to contain the actual set of available backups using the `CROSSCHECK`, `CHANGE`, `CATALOG`, `UNCATALOG` and `DELETE` commands. Otherwise, the output of `REPORT` may be misleading.

## 4.8.2 Reporting on Files Needing a Backup Under a Retention Policy

Use the `REPORT` `NEED` `BACKUP` command to determine which database files need backup under a specific retention policy.

With no arguments, `REPORT` `NEED` `BACKUP` reports which objects need backup under the currently configured retention policy. The output for a configured retention policy of REDUNDANCY 1 is similar to this example:

    REPORT NEED BACKUP;

    RMAN retention policy will be applied to the command
    RMAN retention policy is set to redundancy 1
    Report of files with less than 1 redundant backups
    File #bkps Name
    ---- ----- -----------------------------------------------------
    2    0     /oracle/oradata/trgt/undotbs01.dbf

Note: If you disable the retention policy using `CONFIGURE` `RETENTION` `POLICY` `TO` `NONE`, then `REPORT` `NEED` `BACKUP` returns an error message, because without a retention policy, RMAN cannot determine which files need to be backed up.

### 4.8.2.1 Using RMAN REPORT NEED BACKUP with Different Retention Policies

You can specify different criteria for `REPORT` `NEED` `BACKUP`, using one of the following forms of the command:

- `REPORT` `NEED` `BACKUP` `RECOVERY` `WINDOW` `OF` `n` `DAYS`

  Displays objects requiring backup to satisfy a recovery window-based retention policy.

- `REPORT` `NEED` `BACKUP` `REDUNDANCY` `n`

  Displays objects requiring backup to satisfy a redundancy-based retention policy.

- `REPORT` `NEED` `BACKUP` `DAYS` `=` `n`

  Displays files that require more than `n` days' worth of archived redo log files for recovery.

- `REPORT` `NEED` `BACKUP` `INCREMENTAL` `n`

  Displays files that require application of more than `n` incremental backups for recovery.

### 4.8.2.2 Using RMAN REPORT NEED BACKUP with Tablespaces and Datafiles

`REPORT` `NEED` `BACKUP` can check the entire database, skip specified tablespaces, or check only specific tablespaces or datafiles against different retention policies, as shown in the following examples:

    RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE TBS_2;
    RMAN> REPORT NEED BACKUP REDUNDANCY 2 DATAFILE 1;
    RMAN> REPORT NEED BACKUP TABLESPACE TBS_3; # uses configured retention policy
    RMAN> REPORT NEED BACKUP INCREMENTAL 2; # checks entire database

See Also: Oracle Database Backup and Recovery Reference for all possible options for `REPORT` `NEED` `BACKUP` and an explanation of the various column headings in the output

### 4.8.2.3 Using REPORT NEED BACKUP with Backups onTape or Disk Only

You can limit the backups tested by REPORT NEED BACKUP to disk-based or tape-based backups only, as shown in these examples:

    RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE DEVICE TYPE SBT;
    RMAN> REPORT NEED BACKUP DEVICE TYPE DISK;
    RMAN> REPORT NEED BACKUP TABLESPACE TBS_3 DEVICE TYPE SBT;

## 4.8.3 Reporting on Datafiles Affected by Unrecoverable Operations

When a datafile has been changed by an unrecoverable operation, such as a direct load insert, normal media recovery cannot be used to recover the file, because an unrecoverable operation does not generate redo. You must perform either a full or incremental backup of affected datafiles after such operations, to ensure that data blocks affected by the unrecoverable operation can be recovered using RMAN.

To identify datafiles affected by an unrecoverable operation and the type of backup required to ensure the datafile can be restored from backup, use the `REPORT` `UNRECOVERABLE` command, as shown in this example:

    RMAN> REPORT UNRECOVERABLE;
    Report of files that need backup due to unrecoverable operations
    File Type of Backup Required Name
    ---- ----------------------- -----------------------------------
    1    full                    /oracle/oradata/trgt/system01.dbf

## 4.8.4 Reporting Obsolete Backups

You can report backup sets, backup pieces and datafile copies that are obsolete, that is, not needed to meet a specified retention policy, by specifying the `OBSOLETE` keyword. If you do not specify any other options, then `REPORT` `OBSOLETE` displays the backups that are obsolete according to the current retention policy, as shown in the following example:

    RMAN> REPORT OBSOLETE;
    Datafile Copy        44     08-FEB-05          /backup/ora_df549738566_s70_s1
    Datafile Copy        45     08-FEB-05          /backup/ora_df549738567_s71_s1
    Datafile Copy        46     08-FEB-05          /backup/ora_df549738568_s72_s1
    Backup Set           26     08-FEB-05
      Backup Piece       26     08-FEB-05          /backup/ora_df549738682_s76_s1
    .
    .
    .

You can also check which backups are obsolete under different recovery window-based or redundancy-based retention policies, by using `REPORT` `OBSOLETE` with `RECOVERY` `WINDOW` and `REDUNDANCY` options, as shown in these examples:

    REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS;
    REPORT OBSOLETE REDUNDANCY 1;

You can specify to only consider backups on disk or on tape when checking for obsolete files, by specifying a device type with the `REPORT` command, as in this example:

    REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS DEVICE TYPE DISK;
    REPORT OBSOLETE REDUNDANCY 1;

To report obsolete backups:

1. Connect to your target database and your recovery catalog (if you are using a recovery catalog.)

2. To make sure that your RMAN repository has current information about the status of different backups, you may want to issue `CROSSCHECK` commands to update the status of backups in the repository compared to their status on disk, or use the `CHANGE`, `CATALOG`, `UNCATALOG` and `DELETE` commands to directly specify the status of individual backups.

   In the simplest case, you could crosscheck all backups on disk, tape or both, using any one of the following commands:

       RMAN> CROSSCHECK BACKUP DEVICE TYPE DISK;
       RMAN> CROSSCHECK BACKUP DEVICE TYPE SBT;
       RMAN> CROSSCHECK BACKUP; # crosshecks all backups on all devices

   See Chapter 8, "Recovery Manager Maintenance Tasks" for more details on how to update the RMAN repository record to contain the actual set of available backups.

3. Run `REPORT` `OBSOLETE` to identify which backups are obsolete because they are no longer needed for recovery. Here are several examples:

       # lists backups that not needed to recover the database to within last week
       REPORT OBSOLETE RECOVERY WINDOW OF 7 DAYS;

       # lists backups not needed for redundancy-based retention policy, considering only backups stored on tape
       REPORT OBSOLETE REDUNDANCY = 2 DEVICE TYPE sbt;

See Also: "Configuring the Backup Retention Policy" for a conceptual overview of RMAN backup retention policy "Deleting Expired RMAN Backups after CROSSCHECK" for information on deleting RMAN backups and deleting records of RMAN backups from the RMAN repository

## 4.8.5 Reporting on the Database Schema

The `REPORT` `SCHEMA` command lists and displays information about the database files.

After connecting RMAN to the target database and recovery catalog (if you use one), issue `REPORT` `SCHEMA` as shown in this example:

    RMAN> REPORT SCHEMA;
    List of Permanent Datafiles
    ===========================
    File Size(MB) Tablespace           RB segs Datafile Name
    ---- -------- -------------------- ------- ------------------------
    1    450      SYSTEM               ***     /oracle/oradata/tbs_01.f
    2    50       SYSAUX               ***     /oracle/oradata/tbs_ax1.f
    3    2        SYSTEM               ***     /oracle/oradata/tbs_02.f
    4    2        TBS_1                ***     /oracle/oradata/tbs_11.f
    .
    .
    .
    21   2        TBS_4                ***     /oracle/oradata/tbs_43.f
    22   2        TBS_5                ***     /oracle/oradata/tbs_53.f

    List of Temporary Files
    =======================
    File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
    ---- -------- -------------------- ----------- --------------------
    1    40       TEMP                 32767       /oracle/oradata/tbs_tmp1.f

Note: If you use a recovery catalog, then you can use the `atClause` to specify a past time, SCN, or log sequence number, as shown in these examples of the command:
    REPORT SCHEMA AT TIME 'SYSDATE-14';     # schema 14 days ago
    REPORT SCHEMA AT SCN 1000;              # schema at scn 1000
    REPORT SCHEMA AT SEQUENCE 100 THREAD 1; # schema at sequence 100